<?php if (!defined('BASEPATH')) exit('No direct script access allowed');

class Bill_model extends Base_Model
{
    const TABLE = 'hy_bill';

    public function __construct()
    {
        parent::__construct();
    }

    public function getList($fields = ' * ', $per_page, $limit, $filter):array
    {
        $where = ' status=0 ';
        if ($keyword = $filter['keyword'] ?? '') {
            $where .= " AND (contract_number ='{$keyword}' ";
            $where .= " OR customer_unit like '%{$keyword}%' ";
            $where .= " OR provider like '%{$keyword}%'  ";
            $where .= " OR contract_number = '{$keyword}'  ";
            $where .= " OR name like '%{$keyword}%' ) ";
        }
        if ($starttime = $filter['starttime'] ?? '') {
            $where .= " AND create_time >= {$starttime}";
        }
        if ($endtime = $filter['endtime'] ?? '') {
            $where .= " AND create_time >= {$endtime}";
        }
        $limit = " limit {$per_page},{$limit}";

        $sql = "SELECT {$fields} FROM " . self::TABLE . " WHERE {$where}   ORDER BY contract_number DESC, type asc {$limit} ";

        $query  = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }

    public function searchName($per_page, $limit, $filter):array
    {
        $where = ' status=0 ';
        if ($filter['type'] == 'name' && $name = $filter['name'] ?? '') {
            $where .= " AND name like '%{$name}%'";
        }
        if ($filter['type'] == 'customer_unit' && $name = $filter['name'] ?? '') {
            $where .= " AND customer_unit like '%{$name}%'";
        }
        $limit = " limit {$per_page},{$limit}";

        $sql = "SELECT DISTINCT(".$filter['type'].") FROM " . self::TABLE . " WHERE {$where} ORDER BY create_time DESC {$limit} ";

        $query  = $this->db->query($sql);
        $result = $query->result_array();
        return $result;
    }

    public function getCnt($filter)
    {
        $where = ' status=0 ';
        if ($keyword = $filter['keyword'] ?? '') {
            $where .= " AND (contract_number ='{$keyword}' ";
            $where .= " OR customer_unit like '%{$keyword}%' ";
            $where .= " OR provider like '%{$keyword}%' ";
            $where .= " OR contract_number = '{$keyword}'  ";
            $where .= " OR name like '%{$keyword}%' ) ";
        }
        if ($starttime = $filter['starttime'] ?? '') {
            $where .= " AND create_time >= {$starttime}";
        }
        if ($endtime = $filter['endtime'] ?? '') {
            $where .= " AND create_time >= {$endtime}";
        }
        $sql   = "SELECT bill_id FROM " . self::TABLE . " WHERE {$where}";
        $query = $this->db->query($sql);
        return $query->num_rows();
    }

    public function add($data)
    {
        return $this->db->insert(self::TABLE, $data);
    }

    public function edit($bill_id, $data)
    {
        if (!$bill_id || empty($data)) {
            return false;
        }
        $this->db->where('bill_id', $bill_id);
        return $this->db->update(self::TABLE, $data);
    }

    public function getDetail($bill_id)
    {
        if (!$bill_id) return [];
        $sql   = "SELECT * FROM " . self::TABLE . " WHERE bill_id = {$bill_id}";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    public function  delete($bill_id)
    {
        if (!$bill_id) {
            return false;
        }
        $this->db->where('bill_id', $bill_id);
        $updateData = [
            'status'      => -1,
            'update_time' => date("Y-m-d H:i:s", time()),
        ];
        return $this->db->update(self::TABLE, $updateData);
    }

    public function  deliver($contract_number)
    {
        if (!$contract_number) {
            return false;
        }
        $this->db->where('contract_number', $contract_number);
        $updateData = [
            'deliver_time' => date("Y-m-d H:i:s", time()),
            'update_time'  => date("Y-m-d H:i:s", time()),
        ];
        return $this->db->update(self::TABLE, $updateData);
    }

    public function getHandle()
    {
        $sql   = "SELECT bill_id,name,purchase_price,caigouzongjia,unit_selling_price,xiaoshouzongjia FROM hy_bill WHERE name like '%运费%'";
        $query = $this->db->query($sql);
        $list  = $query->result_array();
        foreach ($list as $bill) {
            if ($bill['purchase_price'] == 0 && $bill['caigouzongjia']) {
                if ($bill['bill_id'] == '21666') {
                    //var_dump($bill);die;
                }
                $this->db->where('bill_id', $bill['bill_id']);
                $this->db->update(self::TABLE, ['purchase_price' => $bill['caigouzongjia']]);
            }
            if ($bill['unit_selling_price'] == 0 && $bill['xiaoshouzongjia']) {
                $this->db->where('bill_id', $bill['bill_id']);
                $this->db->update(self::TABLE, ['unit_selling_price' => $bill['xiaoshouzongjia']]);
            }
        }
        echo "success";
    }

    //采购总价
    public function getPurchaseTotalPrice($filter = [])
    {
        $where = ' ';
        if ($filter) {
            $where .= " AND signed_time >'{$filter['timelimit']}' ";
        }
        $sql    = "SELECT
	              SUM(number * unit_selling_price) AS selling_price,
	              SUM(number * purchase_price) AS puerchase_price
                FROM
	            " . self::TABLE . " WHERE status = 0 {$where}";
        $query  = $this->db->query($sql);
        $result = $query->row_array();
        return $result;
    }

    //获取本周数据
    public function getThisWeekData($this_week)
    {
        $this_week_format = '';
        $week_as_key      = [];
        foreach ($this_week as $key => $row) {
            $this_week_format[$key]      = '"' . $row . '"';
            $week_as_key[$row]['cnt']    = 0;
            $week_as_key[$row]['profit'] = 0;
        }
        $this_week_str = implode(',', $this_week_format);
        $sql           = 'SELECT
                  count(DISTINCT contract_number) cnt, DATE_FORMAT(signed_time,"%Y-%m-%d") signed_time_day, signed_time,SUM(number * unit_selling_price) AS selling_price,
	              SUM(number * purchase_price) AS puerchase_price
                FROM
	              hy_bill
	            WHERE
	            DATE_FORMAT(signed_time, "%Y-%m-%d") in (' . $this_week_str . ')
	            AND
	            status = 0
                GROUP BY
	              DATE_FORMAT(signed_time, "%Y-%m-%d")';
        $query         = $this->db->query($sql);
        $record        = $query->result_array();
        foreach ($record as $row) {
            $week_as_key[$row['signed_time_day']]['cnt']    = $row['cnt'];
            $week_as_key[$row['signed_time_day']]['profit'] = $row['selling_price'] - $row['puerchase_price'];
        }
        $result['days']   = $this_week;
        $result['cnt']    = array_column($week_as_key, 'cnt');
        $result['profit'] = array_column($week_as_key, 'profit');
        return $result;
    }

    //未结算账目
    public function getSettleAccounts($entry_source = 1)
    {
        $sql    = "SELECT
	              SUM(number * purchase_price) AS puerchase_price
                FROM
	            " . self::TABLE . " WHERE status = 0 AND settle_accounts = 1 AND entry_source = {$entry_source}";
        $query  = $this->db->query($sql);
        $result = $query->row_array();
        return $result;
    }

    //获取订单数
    public function getOrdersCnt($filter = [])
    {
        $where = ' ';
        if ($filter) {
            $where .= " AND signed_time >'{$filter['timelimit']}' ";
        }
        $sql    = "SELECT
	              count(distinct contract_number) contract_count
                FROM
	            " . self::TABLE . " WHERE status = 0 {$where}";
        $query  = $this->db->query($sql);
        $result = $query->row_array();
        return $result['contract_count'] ?? 0;
    }

    //获取全年每月的数据
    public function getEveryMonthRecordByYear($year = 2018)
    {
        $sql         = 'SELECT
                  count(DISTINCT contract_number) cnt, DATE_FORMAT(signed_time,"%m") signed_time_month, signed_time,SUM(number * unit_selling_price) AS selling_price,
	              SUM(number * purchase_price) AS puerchase_price
                FROM
	              hy_bill
	            WHERE
	            DATE_FORMAT(signed_time, "%Y") = ' . $year . '
	            AND
	            status = 0
                GROUP BY
	              DATE_FORMAT(signed_time, "%Y-%m")';
        $query       = $this->db->query($sql);
        $record      = $query->result_array();
        $monthRecord = [];
        for ($month = 1; $month <= 12; $month++) {
            $monthRecord[$month] = ['cnt' => 0, 'profit' => 0];
        }
        foreach ($record as $row) {
            $monthRecord[(int)$row['signed_time_month']]['cnt']    = $row['cnt'];
            $monthRecord[(int)$row['signed_time_month']]['profit'] = $row['selling_price'] - $row['puerchase_price'];
        }
        return $monthRecord;
    }

    public function getMaxContractNumber()
    {
        $sql   = "SELECT max(contract_number) contract_number FROM  " . self::TABLE . " WHERE status =0 ";
        $query = $this->db->query($sql);
        return $query->row_array();
    }

    public function getRecordByContractNumber($contract_number, $fields = " * ", $filter = [])
    {
        $where = "";
        if ($type = $filter['type'] ?? '') {
            $where = " AND type = {$type}";
        }
        $sql   = " SELECT {$fields} FROM " . self::TABLE . " WHERE contract_number = '" . $contract_number . "' {$where}";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

    public function getRecordByOrderNumber($order_number, $fields = " * ", $filter = [])
    {
        $where = "";
        if ($type = $filter['type'] ?? '') {
            $where = " AND type = {$type}";
        }
        $sql   = " SELECT {$fields} FROM " . self::TABLE . " WHERE order_number = '" . $order_number . "' {$where}";
        $query = $this->db->query($sql);
        return $query->result_array();
    }

}

